package com.iplant.pdm.serviceImpl.dao.mss;

import com.iplant.base.po.OutResult;
import com.iplant.base.po.Pagination;
import com.iplant.base.po.enums.ExceptionTypes;
import com.iplant.base.utils.general.StringUtils;
import com.iplant.base.utils.sql.BaseDAO;
import com.iplant.basic.service.po.bms.BMSEmployee;
import com.iplant.pdm.service.po.mss.MSSBOM;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;

public class MSSBOMDAO extends BaseDAO {

	private static Logger logger = LoggerFactory.getLogger(MSSBOMDAO.class);
	private static MSSBOMDAO Instance = null;

	private MSSBOMDAO() {
		super();
	}

	public static MSSBOMDAO getInstance() {
		if (Instance == null)
			Instance = new MSSBOMDAO();
		return Instance;
	}

	/**
	 * 自身除开的最大版本号
	 * 
	 * @param wLoginUser
	 * @param wName
	 * @param wErrorCode
	 * @return
	 */
	private int MSS_GetMaxVersion(BMSEmployee wLoginUser, String wName, OutResult<Integer> wErrorCode) {

		int wMaxVersion = 0;
		wErrorCode.set(0);

		try {

			String wSQLText = StringUtils.Format(
					"select Max(SUBSTRING_INDEX(t.Code,CONCAT(t.Name,''.V_''),-1)+0 ) as ItemCount ,t.Name"
							+ "  from  {0}.mss_bom t where locate(t.Name,t.Code)=1 and t.Name=:Name group by Name ;",
					super.getDBName());

			Map<String, Object> wParams = new HashMap<String, Object>();
			wParams.put("Name", wName);

			List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);

			for (Map<String, Object> wSqlDataReader : wQueryResultList) {
				wMaxVersion = StringUtils.parseInt(wSqlDataReader.get("ItemCount"));
			}

		} catch (Exception ex) {
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());

			logger.error("MSS_UpdateBOM", ex);
		}
		return wMaxVersion;
	}

	public synchronized void MSS_UpdateBOM(BMSEmployee wLoginUser, MSSBOM wBOM, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);

		try {

			if (wBOM == null || wBOM.RouteID <= 0 || StringUtils.isEmpty(wBOM.Name)) {
				wErrorCode.set(ExceptionTypes.Parameter.getValue());
				return;
			}

			MSSBOM wBOMO = this.MSS_QueryBOM(wLoginUser, 0, wBOM.Code, wErrorCode);
			if (wBOMO.ID > 0 && wBOMO.ID != wBOM.ID) {
				wErrorCode.set(ExceptionTypes.Duplication.getValue());
				return;
			}

			if (wBOM.ID <= 0 || !wBOM.Code.startsWith(wBOM.Name)) {

				int wVersion = this.MSS_GetMaxVersion(wLoginUser, wBOM.Name, wErrorCode);

				wBOM.Code = StringUtils.Format("{0}.V_{1}", wBOM.Name, String.format("%04d", wVersion + 1));
			}

			Map<String, Object> wParams = new HashMap<String, Object>();

			wParams.put("Name", wBOM.Name);
			wParams.put("Code", wBOM.Code);
			wParams.put("RouteID", wBOM.RouteID);
			wParams.put("TypeID", wBOM.TypeID); 
			wParams.put("LineID", wBOM.LineID);
			wParams.put("ProductID", wBOM.ProductID);
			wParams.put("CustomerID", wBOM.CustomerID);
			wParams.put("Description", wBOM.Description);
			wParams.put("IsStandard", wBOM.IsStandard);
			wParams.put("Active", wBOM.Active);
			wParams.put("EditTime", Calendar.getInstance());
			wParams.put("EditorID", wLoginUser.getID());

			if (wBOM.ID <= 0) {

				wParams.put("CreateTime", Calendar.getInstance());
				wParams.put("CreatorID", wLoginUser.getID());

				wBOM.ID = super.Insert(super.getTableName("mss_bom"), wParams);

			} else {
				wParams.put("ID", wBOM.ID);

				super.Update(super.getTableName("mss_bom"), "ID", wParams);
			}

		} catch (Exception ex) {
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());

			logger.error("MSS_UpdateBOM", ex);
		}
	}

	public void MSS_SetBOMToStandard(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);
		// 判断客户信息是否存在(中国：统一社会信用代码，国外:提醒是否有重复）

		try {
			if (wID <= 0)
				return;

			MSSBOM wMSSBOM = this.MSS_QueryBOM(wLoginUser, wID, wErrorCode);
			if (wMSSBOM == null || wMSSBOM.ID <= 0 || wMSSBOM.Active != 1)
				return;

			Map<String, Object> wParams = new HashMap<String, Object>();
			String wSQLText = StringUtils.Format(
					"update {0}.mss_bom set IsStandard=0 where ((LineID =:LineID"
							+ " and  ProductID =:ProductID  and  CustomerID =:CustomerID"
							+ " and LineID=:LineID ) or RouteID=:RouteID )" + " and IsStandard=1 ;",
					super.getDBName());

			wParams.clear();

			wParams.put("ID", wMSSBOM.ID);
			wParams.put("LineID", wMSSBOM.LineID);
			wParams.put("ProductID", wMSSBOM.ProductID);
			wParams.put("CustomerID", wMSSBOM.CustomerID);
			wParams.put("RouteID", wMSSBOM.RouteID);

			wSQLText = this.DMLChange(wSQLText);
			super.update(wSQLText, wParams);

			wSQLText = StringUtils.Format(" update {0}.mss_bom set IsStandard=1 where ID=:ID and Active = 1 ; ",
					super.getDBName());
			wParams.clear();
			wParams.put("ID", wMSSBOM.ID);
			wSQLText = this.DMLChange(wSQLText);
			super.update(wSQLText, wParams);

		} catch (Exception ex) {
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
			logger.error("MSS_SetRouteToStandard", ex);
		}
	}

	public void MSS_DeleteBOM(BMSEmployee wLoginUser, MSSBOM wBOM, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);

		try {

			if (wBOM == null || wBOM.ID <= 0) {
				wErrorCode.set(ExceptionTypes.Parameter.getValue());
				return;
			}

			Map<String, Object> wParams = new HashMap<String, Object>();

			String wSQLText = StringUtils.Format("Delete from {0}.mss_bom  Where ID=:ID AND Active=0;",
					super.getDBName());

			wParams.put("ID", wBOM.ID);
			wSQLText = this.DMLChange(wSQLText);
			int wRow = super.update(wSQLText, wParams);
			if (wRow > 0) {
				wSQLText = StringUtils.Format("Delete from {0}.mss_bomitem  Where ID>0 AND BOMID=:ID ;",
						super.getDBName());
				super.update(wSQLText, wParams);
			}

		} catch (Exception ex) {

			wErrorCode.set(ExceptionTypes.DBSQL.getValue());

			logger.error("MSS_DeleteMOM", ex);
		}
	}

	public void MSS_ActiveBOM(BMSEmployee wLoginUser, MSSBOM wBOM, int wActive, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);

		try {
			if (wBOM == null || wBOM.ID <= 0) {
				wErrorCode.set(ExceptionTypes.Parameter.getValue());
				return;
			}
			Map<String, Object> wParams = new HashMap<String, Object>();

			String wSQLText = StringUtils.Format(
					"Update {0}.mss_bom Set Active=:Active,EditorID=:EditorID,EditTime=now() Where ID=:ID",
					super.getDBName());
			wParams.clear();
			wParams.put("Active", wActive == 1 ? wActive : 2);
			wParams.put("EditorID", wLoginUser.getID());
			wParams.put("ID", wBOM.ID);
			wSQLText = this.DMLChange(wSQLText);
			super.update(wSQLText, wParams);
		} catch (Exception ex) {

			wErrorCode.set(ExceptionTypes.DBSQL.getValue());

			logger.error("MSS_ActiveBOM", ex);
		}
	}

	public List<MSSBOM> MSS_QueryBOMList(BMSEmployee wLoginUser, String wName, String wCodeLike, int wRouteID,
			String wRouteCodeLike, int wFactoryID, int wLineID, int wCustomerID, int wProductTypeID, int wProductID,
			String wProductNo, int wMaterialID, String wMaterialNo, int wTypeID, int wActive, int wIsStandard,
			Pagination wPagination, OutResult<Integer> wErrorCode) {

		return this.MSS_QueryBOMList(wLoginUser, -1, "", wName, wCodeLike, wRouteID, wRouteCodeLike, wFactoryID,
				wLineID, wCustomerID, wProductTypeID, wProductID, wProductNo, wMaterialID, wMaterialNo, wTypeID,
				wActive, wIsStandard, wPagination, wErrorCode);

	}

	private List<MSSBOM> MSS_QueryBOMList(BMSEmployee wLoginUser, int wID, String wCode, String wName, String wCodeLike,
			int wRouteID, String wRouteCodeLike, int wFactoryID, int wLineID, int wCustomerID, int wProductTypeID,
			int wProductID, String wProductNo, int wMaterialID, String wMaterialNo, int wTypeID, int wActive,
			int wIsStandard, Pagination wPagination, OutResult<Integer> wErrorCode) {
		List<MSSBOM> wBOMList = new ArrayList<MSSBOM>();
		wErrorCode.set(0);

		try {

			// Step02:查询
			Map<String, Object> wParams = new HashMap<String, Object>();

			String wSQLText = StringUtils.Format(
					"Select t.*,t1.Name as RouteName,t1.Code as RouteCode,Count(t11.ID) AS ItemCount,"
							+ " t1.Version as RouteVersion, t2.Name as TypeName,t10.FactoryID,t3.Name as FactoryName,"
							+ " t3.Code as FactoryCode, t6.Name as ProductName,t4.Name as CreatorName,"
							+ " t5.Name as EditorName, t6.Code as  ProductNo ,t6.ProductTypeID,"
							+ " t6.MaterialID,t7.Name as ProductTypeName,t7.Code as ProductTypeCode,"
							+ " t8.MaterialName,t8.Code as  MaterialNo, t9.Name as CustomerName,"
							+ " t9.Code as CustomerCode,t10.Name as LineName,t10.Code as LineCode from {0}.mss_bom t"
							+ " left join {0}.fpc_route t1 on t.RouteID=t1.ID "
							+ " left join {0}.mss_bomtype t2 on t.TypeID=t2.ID "
							+ " left join {0}.mbs_user t4 on t.CreatorID=t4.ID "
							+ " left join {0}.mbs_user t5 on t.EditorID=t5.ID"
							+ " left join {0}.fpc_product t6 on t.ProductID=t6.ID"
							+ " left join {0}.fpc_producttype t7 on t6.ProductTypeID=t7.ID"
							+ " left join {0}.mss_material t8 on t6.MaterialID=t8.ID "
							+ " left join {0}.crm_customer t9 on t.CustomerID=t9.ID "
							+ " left join {0}.fmc_line t10 on t.LineID=t10.ID "
							+ " left join {0}.fmc_factory t3 on t10.FactoryID=t3.ID "
							+ " left join {0}.mss_bomitem t11 on t.ID=t11.BOMID  where (:ID<=0 or t.ID=:ID) "
							+ " and (:Name = '''' or :Name = t.Name) and (:Code = '''' or :Code = t.Code)"
							+ " and (:RouteID<=0 or :RouteID = t.RouteID) "
							+ " and (:FactoryID<=0 or :FactoryID = t10.FactoryID) and  (:LineID<=0 or t.LineID=:LineID) "
							+ " and (:CustomerID<=0 or :CustomerID = t.CustomerID)"
							+ " and (:ProductTypeID<=0 or :ProductTypeID = t6.ProductTypeID) "
							+ " and (:ProductID<=0 or :ProductID = t.ProductID) "
							+ " and (:MaterialID<=0 or :MaterialID = t6.MaterialID) "
							+ " and (:TypeID<=0 or :TypeID = t.TypeID)  and (:Active< 0 or :Active = t.Active) "
							+ " and (:IsStandard< 0 or :IsStandard = t.IsStandard) "
							+ " and (:ProductNo = '''' or :ProductNo = t6.Code) "
							+ " and (:MaterialNo = '''' or :MaterialNo = t8.Code) "
							+ " and (:CodeLike = '''' or  t.Code like :CodeLike) "
							+ " and (:RouteCodeLike = '''' or  t1.Code like :RouteCodeLike)  group by t.ID ",
					super.getDBName());

			wSQLText = this.DMLChange(wSQLText);

			wParams.put("ID", wID);
			wParams.put("Name", wName);
			wParams.put("Code", wCode);
			wParams.put("RouteID", wRouteID);
			wParams.put("FactoryID", wFactoryID);
			wParams.put("LineID", wLineID);
			wParams.put("CustomerID", wCustomerID);
			wParams.put("ProductTypeID", wProductTypeID);
			wParams.put("ProductID", wProductID);
			wParams.put("MaterialID", wMaterialID);
			wParams.put("TypeID", wTypeID);
			wParams.put("Active", wActive);
			wParams.put("IsStandard", wIsStandard);
			wParams.put("ProductNo", wProductNo);
			wParams.put("MaterialNo", wMaterialNo);
			wParams.put("CustomerID", wCustomerID);
			wParams.put("CodeLike", wCodeLike);
			wParams.put("RouteCodeLike", wRouteCodeLike);

			List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams, wPagination);

			MSSBOM wBOM = null;
			for (Map<String, Object> wSqlDataReader : wQueryResultList) {
				wBOM = new MSSBOM();
				wBOM.ID = StringUtils.parseInt(wSqlDataReader.get("ID"));
				wBOM.Code = StringUtils.parseString(wSqlDataReader.get("Code"));
				wBOM.Name = StringUtils.parseString(wSqlDataReader.get("Name"));
				wBOM.RouteID = StringUtils.parseInt(wSqlDataReader.get("RouteID"));
				wBOM.RouteName = StringUtils.parseString(wSqlDataReader.get("RouteName"));
				wBOM.RouteCode = StringUtils.parseString(wSqlDataReader.get("RouteCode"));
				wBOM.RouteVersion = StringUtils.parseString(wSqlDataReader.get("RouteVersion"));
				wBOM.TypeID = StringUtils.parseInt(wSqlDataReader.get("TypeID"));
				wBOM.TypeName = StringUtils.parseString(wSqlDataReader.get("TypeName"));
				wBOM.FactoryID = StringUtils.parseInt(wSqlDataReader.get("FactoryID"));
				wBOM.FactoryCode = StringUtils.parseString(wSqlDataReader.get("FactoryCode"));
				wBOM.FactoryName = StringUtils.parseString(wSqlDataReader.get("FactoryName"));
				wBOM.ProductTypeID = StringUtils.parseInt(wSqlDataReader.get("ProductTypeID"));
				wBOM.ProductTypeCode = StringUtils.parseString(wSqlDataReader.get("ProductTypeCode"));
				wBOM.ProductTypeName = StringUtils.parseString(wSqlDataReader.get("ProductTypeName"));
				wBOM.LineID = StringUtils.parseInt(wSqlDataReader.get("LineID"));
				wBOM.LineName = StringUtils.parseString(wSqlDataReader.get("LineName"));
				wBOM.LineCode = StringUtils.parseString(wSqlDataReader.get("LineCode"));
				wBOM.ProductID = StringUtils.parseInt(wSqlDataReader.get("ProductID"));
				wBOM.ProductNo = StringUtils.parseString(wSqlDataReader.get("ProductNo"));
				wBOM.ProductName = StringUtils.parseString(wSqlDataReader.get("ProductName"));
				wBOM.MaterialID = StringUtils.parseInt(wSqlDataReader.get("MaterialID"));
				wBOM.MaterialNo = StringUtils.parseString(wSqlDataReader.get("MaterialNo"));
				wBOM.MaterialName = StringUtils.parseString(wSqlDataReader.get("MaterialName"));

				wBOM.CustomerID = StringUtils.parseInt(wSqlDataReader.get("CustomerID"));
				wBOM.CustomerCode = StringUtils.parseString(wSqlDataReader.get("CustomerCode"));
				wBOM.CustomerName = StringUtils.parseString(wSqlDataReader.get("CustomerName"));

				wBOM.ItemCount = StringUtils.parseInt(wSqlDataReader.get("ItemCount"));
				wBOM.IsStandard = StringUtils.parseInt(wSqlDataReader.get("IsStandard"));
				wBOM.Active = StringUtils.parseInt(wSqlDataReader.get("Active"));
				wBOM.CreatorID = StringUtils.parseInt(wSqlDataReader.get("CreatorID"));
				wBOM.EditorID = StringUtils.parseInt(wSqlDataReader.get("EditorID"));
				wBOM.CreatorName = StringUtils.parseString(wSqlDataReader.get("CreatorName"));
				wBOM.EditorName = StringUtils.parseString(wSqlDataReader.get("EditorName"));
				wBOM.CreateTime = StringUtils.parseCalendar(wSqlDataReader.get("CreateTime"));
				wBOM.EditTime = StringUtils.parseCalendar(wSqlDataReader.get("EditTime"));

				wBOMList.add(wBOM);
			}

			// Step03:配置系统

		} catch (Exception ex) {
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
			logger.error("MSS_QueryBOMList", ex);
		}
		return wBOMList;
	}

	public MSSBOM MSS_QueryBOM(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {

		return this.MSS_QueryBOM(wLoginUser, wID, "", wErrorCode);

	}

	public MSSBOM MSS_QueryBOM(BMSEmployee wLoginUser, String wCode, OutResult<Integer> wErrorCode) {

		return this.MSS_QueryBOM(wLoginUser, -1, wCode, wErrorCode);

	}

	public MSSBOM MSS_QueryBOMStandard(BMSEmployee wLoginUser, int wRouteID, OutResult<Integer> wErrorCode) {
		MSSBOM wBOM = new MSSBOM();
		try {
			if (wRouteID <= 0)
				return wBOM;

			List<MSSBOM> wMSSBOMList = this.MSS_QueryBOMList(wLoginUser, -1, "", "", "", wRouteID, "", -1, -1, -1, -1,
					-1, "", -1, "", -1, 1, 1, Pagination.getDefault(), wErrorCode);
			if (wMSSBOMList != null && wMSSBOMList.size() > 0)
				wBOM = wMSSBOMList.get(0);

		} catch (Exception ex) {
			wErrorCode.set(ExceptionTypes.Exception.getValue());
			logger.error("MSS_QueryBOMStandard", ex);
		}
		return wBOM;
	}

	public MSSBOM MSS_QueryBOM(BMSEmployee wLoginUser, int wID, String wCode, OutResult<Integer> wErrorCode) {
		MSSBOM wBOM = new MSSBOM();
		try {
			if (wID <= 0 && StringUtils.isEmpty(wCode))
				return wBOM;

			List<MSSBOM> wMSSBOMList = this.MSS_QueryBOMList(wLoginUser, wID, wCode, "", "", -1, "", -1, -1, -1, -1, -1,
					"", -1, "", -1, -1, -1, Pagination.getDefault(), wErrorCode);
			if (wMSSBOMList != null && wMSSBOMList.size() > 0)
				wBOM = wMSSBOMList.get(0);

		} catch (Exception ex) {
			wErrorCode.set(ExceptionTypes.Exception.getValue());
			logger.error("MSS_QueryBOM", ex);
		}
		return wBOM;
	}

}
